
--- Update Trang Thai Gui Mail Duyet ---

Declare @KhoaPhieuGhiDanh bigint
SET @KhoaPhieuGhiDanh = 1

UPDATE PhieuGhiDanh
SET TrangThaiGuiMailDuyet = 1
WHERE Khoa = @KhoaPhieuGhiDanh AND TrangThaiPhieuGhiDanh = 2

SELECT PGD.Khoa, PGD.KhoaHoc as KhoaHocID, KH.MaKhoaHoc, PGD.DoiTuongGhiDanh as DoiTuongGhiDanhID,
	   DT.MaDoiTuong, DT.TenDoiTuong, DT.CMND, DT.NgaySinh, DT.DiaChiThuongTru, 
	   GP.Khoa as GiayPhepLaiXeID, GP.SoGiayPhepLaiXe as SoGiayPhepLaiXeDaCo, 
	   HXGP.MaHangXe as HangXeGiayPhep, GP.NoiCap as NoiCapGiayPhep, GP.NgayCap as NgayCapGiayPhep,
	   HXDK.Khoa as HangXeID, HXDK.MaHangXe as HangXeDangKy,
	   PGD.DaNopGiayKhamSucKhoe, PGD.DaNopCMND, PGD.DaNopDuAnh, PGD.HocNgoaiGio,
	   PGD.TrangThaiPhieuGhiDanh, TrangThaiGuiMailDangKy, PGD.TrangThaiGuiMailDuyet, PGD.MaLoaiDangKy, LDK.TenLoaiDangKy, 
	   PGD.KhoaNhanVienDangKy, NVDK.TenNhanVien as TenNhanVienDangKy, PGD.KhoaNhanVienDuyet, NVDY.TenNhanVien as TenNhanVienDangKy
FROM PhieuGhiDanh PGD
	LEFT JOIN KhoaHoc KH ON PGD.KhoaHoc = KH.Khoa
	LEFT JOIN DoiTuongGhiDanh DT ON PGD.DoiTuongGhiDanh = DT.Khoa
	LEFT JOIN GiayPhepLaiXe GP ON DT.GiayPhepLaiXeDaCo = GP.Khoa
	LEFT JOIN HangXe HXGP ON GP.HangXe = HXGP.Khoa 
	LEFT JOIN HangXe HXDK ON PGD.HangXe = HXDK.Khoa
	LEFT JOIN LoaiDangKy LDK ON PGD.MaLoaiDangKy = LDK.MaLoaiDangKy
	LEFT JOIN NhanVien NVDK ON PGD.KhoaNhanVienDangKy = NVDK.Khoa
	LEFT JOIN NhanVien NVDY ON PGD.KhoaNhanVienDuyet = NVDY.Khoa
WHERE PGD.TrangThaiPhieuGhiDanh = 2


